package example.dbutility;


import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.derby.tools.ij;

public class DBUtility {
	
	private static final String PJ_HOME = "../DataLayerWithFactory";
	
	private static final String INIT_DATA = PJ_HOME+"/scripts/init.sql";
	private static final String CREATE_DERBY_DB = PJ_HOME+"/scripts/create.sql";
	
	private static String DATABASE_NAME = "DATABASE";
	
	
	public static void initDerbyDb() {
		checkAndDropDatabase();
		System.out.println();
		createDerbyDb();
		System.out.println();
	}
	
	private static void createDerbyDb() {
		String url = "jdbc:derby:"+DATABASE_NAME+";create=true";
		String driver =  "org.apache.derby.jdbc.EmbeddedDriver";
		Connection conn = null;		
		try {

			Class.forName(driver);
            conn = DriverManager.getConnection(url);
            
           

			// Create tables based on script
			System.out.println("Creating db...");
			FileInputStream createScript = new FileInputStream(CREATE_DERBY_DB);
			ij.runScript(conn, createScript, "UTF-8", System.out,"UTF-8");
			System.out.println("Db created");
			System.out.println("Init Data");
			FileInputStream initData = new FileInputStream(INIT_DATA);
			ij.runScript(conn, initData, "UTF-8", System.out,"UTF-8");
			System.out.println("Data initialized");
				
			conn.commit();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	private static void checkAndDropDatabase() {
		//System.out.println("checkAndDropDatabase");
		List<File> pathDatabaseToDeletes = searchDatabasePath();
		for (File pathDatabaseToDelete: pathDatabaseToDeletes) {
			System.out.println("checkAndDropDatabase - pathDatabaseToDelete: "+pathDatabaseToDelete.getAbsolutePath());
			System.out.println("checkAndDropDatabase - pathDatabaseToDelete exists: "+pathDatabaseToDelete.exists());			
			if (pathDatabaseToDelete.exists()) {
				System.out.println("Shutdown db....");
				shutDownDatabase();				
				System.out.println("checkAndDropDatabase - delete database at: "+pathDatabaseToDelete);
				deleteDirectory(pathDatabaseToDelete);
			}
		}
	}
	
	private static List<File> searchDatabasePath() {
		//System.out.println("searchDatabasePath");
		File start = new File(PJ_HOME);
		ArrayList<File> results = new ArrayList<File>();
		searchDatabasePath(start,results);
		return results;
	}
	
	private static void searchDatabasePath(File start, List<File> results) {
		//System.out.println("start"+start);
		if (start.isDirectory() && start.getName().equals(DATABASE_NAME))
			results.add(start);
		if (start.isDirectory()) {
			File[] files = start.listFiles();
			if (files.length>0) {
				for (File file: files) {
					if (file.isDirectory()) {
						searchDatabasePath(file,results);
					}
				}
			}
		}			
	}
	
	private static boolean deleteDirectory(File path) {
		//System.out.println("deleteDirectory");
		if (path.exists()) {
			File[] files = path.listFiles();
			for(File file: files) {
				if(file.isDirectory())
					deleteDirectory(file);
				else
					file.delete();
			}
	    }
		System.out.println("Delete dir: "+path.getAbsolutePath());
	    return (path.delete());
	}
	
	private static void shutDownDatabase() {
		try {
			// Before to drop any database we need to be sure that the database is shoutdown
			// ERROR 08006 is not an error: it confirms a successful shutdown. exit terminates the IJ application.
			DriverManager.getConnection("jdbc:derby:"+DATABASE_NAME+";shutdown=true");
		} catch (Exception e) {
			//e2.printStackTrace();
		}
	}

}
